package common.main.cnkiJournalPaper;

import common.main.common.BaseSaveDataToSql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;


public class CnkiJournalPaperSaveDataToSql extends BaseSaveDataToSql {


    public static List<String> getAllJournalNames(){
        List<String> journalNameList = new ArrayList<>();
        try{
            connectDatabase();
            String sql = "SELECT `name` FROM `datacrawl_journal` where `language` = 'cn'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                String journalName = rs.getString("name");
                if(journalName != null){
                    journalNameList.add(journalName);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return journalNameList;
    }

    public static void main(String[] args) {
        Calendar calendar = Calendar.getInstance();
        System.out.println(calendar.get(calendar.YEAR));
    }

    public static String getAllMd5(){
        try{
            connectDatabase();
            String sql = "select Md5 from dataSource_paper where Source = 'cnki'";
            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("Md5") + ",");
            }
            if(sb == null){
                return "";
            }
            return sb.toString();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void saveCnkiJournalPaperToSql(List<CnkiJournalPaperData> paperList){
        try{
            connectDatabase();

            String sql_model = "INSERT INTO dataSource_paper (Title,Authors,AffiliationOld,Affiliation,Journal,CiteHistory,DownloadNumber,Brief," +
                    "Fund,Keywords,ClassNumber,Page,Issue,md5,PaperClassification,Source,Language,PaperUrl)" +
                    "values ('<title>','<author>','<affiliation>','<authorAffiliation>','<journal>','<cite_num>',<download_num>,'<brief>'," +
                    "'<fund>','<keywords>','<class_num>','<page>','<issue>','<md5>','Journal','cnki','cn','<paperUrl>')";
            for(int i = 0;i < paperList.size();i++){
                try{
                    CnkiJournalPaperData paperData = paperList.get(i);

                    paperData.setTitle(paperData.getTitle().replace("'","\\'"));
                    paperData.setAuthor(paperData.getAuthor().replace("'","\\'"));
                    paperData.setAffiliation(paperData.getAffiliation().replace("'","\\'"));
                    paperData.setAuthorAffiliation(paperData.getAuthorAffiliation().replace("'","\\'"));
                    paperData.setJournal(paperData.getJournal().replace("'","\\'"));
                    paperData.setBrief(paperData.getBrief().replace("'","\\'"));
                    paperData.setFund(paperData.getFund().replace("'","\\'").replace("\n" +
                            "                  ",""));
                    paperData.setKeywords(paperData.getKeywords().replace("'","\\'").replace("\n" +
                            "                  ",""));
                    paperData.setPaperUrl(paperData.getPaperUrl().replace("'","\\'").trim());

                    if(paperData.getDownload_num() == null || "".equals(paperData.getDownload_num())){
                        paperData.setDownload_num("-1");
                    }
                    String sql = sql_model.replace("<title>",paperData.getTitle())
                            .replace("<author>",paperData.getAuthor())
                            .replace("<affiliation>",paperData.getAffiliation())
                            .replace("<authorAffiliation>",paperData.getAuthorAffiliation())
                            .replace("<journal>",paperData.getJournal())
                            .replace("<cite_num>",paperData.getCite_num())
                            .replace("<download_num>",paperData.getDownload_num())
                            .replace("<brief>",paperData.getBrief())
                            .replace("<fund>",paperData.getFund())
                            .replace("<keywords>",paperData.getKeywords())
                            .replace("<class_num>",paperData.getClass_num())
                            .replace("<page>",paperData.getPage())
                            .replace("<issue>",paperData.getIssue())
                            .replace("<md5>",paperData.getMd5())
                            .replace("<paperUrl>",paperData.getPaperUrl());

                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
